Imports Microsoft.VisualBasic

Imports System.Data
Imports System.Data.SqlClient

Public Class Generation
   
    Public Shared Sub setDataExcuite(ByVal sql As String)
        Dim settings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("Conn")
        Dim conn As New SqlClient.SqlConnection(settings.ConnectionString)

        Try
            conn.Open()
            Dim myCmd As New SqlClient.SqlCommand(sql, conn)
            myCmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw New Exception(ex.ToString)
        Finally
            If Not conn Is Nothing Then
                conn.Close()
                conn = Nothing
            End If
        End Try
    End Sub

    Public Shared Function getDataTable(ByVal sql As String, ByVal lParamArray As SqlParameter()) As DataTable
        Try
            Dim ds As DataSet = getDataSet(sql, lParamArray)

            If ds IsNot Nothing Then
                Return ds.Tables(0)
            End If
        Catch ex As Exception
            Throw New Exception(ex.ToString())
        End Try

        Return Nothing
    End Function
     
    Public Shared Function getDataSet(ByVal sql As String, ByVal lParamArray As SqlParameter()) As DataSet
        Dim dsResult As DataSet = Nothing
        Dim conn As SqlConnection = Nothing
        Try
            conn = CommonUtil.GetConnection()
            Using conn
                dsResult = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql, lParamArray)
            End Using
        Catch ex As Exception
            Throw New Exception(ex.ToString())
        Finally
            If conn IsNot Nothing Then
                conn.Close()
                conn.Dispose()
                conn = Nothing
            End If
        End Try

        Return dsResult
    End Function
    Private Shared Function getCondition(ByVal keys As String(), ByVal vals As String()) As String
        Dim w As String = Nothing
        Dim i As Integer = 0
        For i = 0 To keys.Length - 1
            If w Is Nothing Then
                w = keys(i) & "=" & vals(i)
            Else
                w += " AND " & keys(i) & "=" & vals(i)
            End If
        Next
        Return w
    End Function
    Public Shared Function getID(ByVal keys As String(), ByVal vals As String(), ByVal tablename As String) As DataRow
        If (keys.Length <> vals.Length) Then
            Return Nothing
        End If
        Dim con As SqlConnection = Nothing

        Dim ds As DataSet = Nothing
        Dim sql As String = "SELECT * FROM " & tablename & _
                            " WHERE " & getCondition(keys, vals)
        Try
            Try
                con = New DBAccess().GetSqlConnection()
            Catch e As System.Exception
                Return Nothing
            End Try

            ds = SqlHelper.ExecuteDataset(con, CommandType.Text, sql)
            If (ds.Tables(0).Rows.Count > 0) Then
                Return ds.Tables(0).Rows(0)
            End If
        Catch ex As Exception
            Throw New Exception(ex.ToString())

        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
            End If
            If ds IsNot Nothing Then
                ds = Nothing
            End If
        End Try
        Return Nothing
    End Function
    Private Shared Function getParameterInsert(ByVal row As DataRow) As String
        Dim col As DataColumn
        Dim str As String = Nothing
        For Each col In row.Table.Columns
            If (str Is Nothing) Then
                str = "@" & col.ColumnName
            Else
                str += ", @" & col.ColumnName
            End If
        Next

        Return str
    End Function
    Public Shared Function InsertItem(ByVal row As DataRow) As Integer
        If (row IsNot Nothing) Then
            Dim con As SqlConnection = Nothing
            Try
                'Get the connection
                con = New DBAccess().GetSqlConnection
                If con Is Nothing Then
                    Return 0
                    'Connection is not created 
                End If
                Dim sql As String = "SELECT * FROM " & row.Table.TableName & " WHERE 1=0"
                Dim ds As DataSet = SqlHelper.ExecuteDataset(con, CommandType.Text, sql)
                Dim row_tmp As DataRow = ds.Tables(0).NewRow()

                Dim arIndex As New ArrayList
                Dim col As DataColumn
                For Each col In row_tmp.Table.Columns
                    If row.Table.Columns.Contains(col.ColumnName) = False Then
                        arIndex.Add(col.ColumnName)
                    End If
                Next
                Dim ie As System.Collections.IEnumerator = arIndex.GetEnumerator()

                While (ie.MoveNext)
                    row_tmp.Table.Columns.Remove(ie.Current)
                End While
                arIndex = Nothing
                ds = Nothing
                sql = "INSERT INTO " & row.Table.TableName & "  (" + getParameterInsert(row_tmp).Replace("@", "") + ")" + " VALUES (" & getParameterInsert(row_tmp) & ")"
                Dim lParamArray As SqlParameter() = Nothing
                Dim arr As New ArrayList
                For Each col In row_tmp.Table.Columns
                    Dim param As New SqlParameter("@" & col.ColumnName, row(col.ColumnName))
                    arr.Add(param)
                Next
                row_tmp = Nothing
                lParamArray = CType(arr.ToArray(GetType(System.Data.SqlClient.SqlParameter)), SqlParameter())
                Dim i As Integer
                i = SqlHelper.ExecuteScalar(con, CommandType.Text, sql, lParamArray)
                If i > 0 Then
                    Return i
                Else
                    Return 0
                End If
            Catch ex As Exception
                Throw New Exception(ex.ToString())
                ' error out
            Finally
                If con IsNot Nothing Then
                    con.Close()
                    con.Dispose()
                End If
            End Try
        End If
        Return 0
    End Function
    Private Shared Function getParameterUpdate(ByVal row As DataRow) As String
        Dim col As DataColumn
        Dim str As String = Nothing
        For Each col In row.Table.Columns
            If (str Is Nothing) Then
                str = col.ColumnName & "=@" & col.ColumnName
            Else
                str += ", " & col.ColumnName & "=@" & col.ColumnName
            End If
        Next

        Return str
    End Function
    Public Shared Function UpdateItem(ByVal keys As String(), ByVal vals As String(), ByVal tablename As String, ByVal row As DataRow) As Boolean
        If row IsNot Nothing Then
            Dim con As SqlConnection = Nothing
            Try
                'Get the connection
                con = New DBAccess().GetSqlConnection
                If con Is Nothing Then
                    Return False
                    'Connection is not created 
                End If
                Dim sql As String = "SELECT * FROM " & row.Table.TableName & " WHERE 1=0"
                Dim ds As DataSet = SqlHelper.ExecuteDataset(con, CommandType.Text, sql)
                Dim row_tmp As DataRow = ds.Tables(0).NewRow()

                Dim arIndex As New ArrayList
                Dim col As DataColumn
                For Each col In row_tmp.Table.Columns
                    If row.Table.Columns.Contains(col.ColumnName) = False Then
                        arIndex.Add(col.ColumnName)
                    End If
                Next
                Dim ie As System.Collections.IEnumerator = arIndex.GetEnumerator()

                While (ie.MoveNext)
                    row_tmp.Table.Columns.Remove(ie.Current)
                End While
                arIndex = Nothing
                ds = Nothing


                sql = "UPDATE  " & tablename & _
                                    " SET " & getParameterUpdate(row_tmp) & _
                                    " WHERE " & getCondition(keys, vals)
                'Get the parameter list needed by the given object
                Dim lParamArray As SqlParameter() = Nothing
                Dim arr As New ArrayList
                For Each col In row_tmp.Table.Columns
                    Dim param As New SqlParameter("@" & col.ColumnName, row(col.ColumnName))
                    arr.Add(param)
                Next
                row_tmp = Nothing
                lParamArray = CType(arr.ToArray(GetType(System.Data.SqlClient.SqlParameter)), SqlParameter())

                'Execute the insertion
                Dim i As Integer = SqlHelper.ExecuteNonQuery(con, CommandType.Text, sql, lParamArray)
                If i > 0 Then
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception
                Throw New Exception(ex.ToString())
            Finally
                If con IsNot Nothing Then
                    con.Close()
                    con.Dispose()
                End If
            End Try
        Else
            Return False
        End If
    End Function
    Public Shared Function DeleteItem(ByVal keys As String(), ByVal vals As String(), ByVal tablename As String) As Boolean
        If (keys.Length <> vals.Length) Then
            Return False
        End If
        Dim ds As DataSet = Nothing
        Dim sql As String = "DELETE FROM " & tablename & _
                            " WHERE " & getCondition(keys, vals)
        Dim con As SqlConnection = Nothing
        Try
            'Get the connection
            con = New DBAccess().GetSqlConnection
            If con Is Nothing Then
                Return False
                'Connection is not created 
            End If
            Dim i As Integer = SqlHelper.ExecuteNonQuery(con, CommandType.Text, sql)
            Return True
        Catch ex As Exception
            Throw New Exception(ex.ToString())
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
            End If
        End Try
        Return False
    End Function
  
    Public Shared Function ExecuteNonQuery(ByVal sql As String, ByVal lParamArray As SqlParameter()) As Boolean
        Dim con As SqlConnection = Nothing
        Try
            'Get the connection
            con = New DBAccess().GetSqlConnection
            If con Is Nothing Then
                Throw New Exception("Cannot connect to database")
            End If
            Dim i As Integer = SqlHelper.ExecuteNonQuery(con, CommandType.Text, sql, lParamArray)
            Return (i > 0)
        Catch ex As Exception
            Throw New Exception(ex.ToString())
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
            End If
        End Try
        Return False
    End Function
    Public Shared Function getWhereIn(ByVal dt As DataTable, ByVal columnName As String) As String
        Dim str As String = ""
        If (dt.Columns.Contains(columnName)) Then
            Dim ch As String = ""
            If (dt.Columns(columnName).DataType Is Type.GetType("System.String")) Then
                ch = "'"
            End If
            Dim r As DataRow
            For Each r In dt.Rows
                If (str = "") Then
                    str = ch & r(columnName) & ch
                Else
                    str += ", " & ch & r(columnName) & ch
                End If
            Next
        End If
        If (str.Length > 0) Then
            str = "(" & str & ")"
        End If
        Return str
    End Function

    Public Shared Function getWhereIn2(ByVal dt As DataTable, ByVal columnName As ArrayList) As String
        Dim str As String = ""
        Dim count As Integer = columnName.Count
        Dim i As Integer = 0
        Dim j As Integer = 0
        If (dt.Columns.Contains(columnName(0))) Then
            Dim ch As String = ""
            If (dt.Columns(columnName(0)).DataType Is Type.GetType("System.String")) Then
                ch = "'"
            End If
            Dim r As DataRow
            'Next
            Dim len As Integer = dt.Rows.Count
            For Each r In dt.Rows
                For i = 0 To count - 1
                    If (str = "") Then
                        str = ch & CStr(r(columnName(i)))
                    Else
                        If (i = (count - 1)) Then
                            If (j = len) Then
                                str += CStr(r(columnName(i)))
                            Else
                                str += CStr(r(columnName(i))) & ch
                            End If

                        Else
                            str += CStr(r(columnName(i)))
                        End If

                    End If
                Next
                j += 1
                If (j <> len) Then
                    str += ", " & ch
                End If
            Next

        End If
        If (str.Length > 0) Then
            str = "(" & str & ")"
        End If
        Return str
    End Function
    Public Shared Sub UpdateTable(ByVal dt As Data.DataTable)
        Dim con As SqlConnection = Nothing
        Try
            'Get the connection
            con = New DBAccess().GetSqlConnection
            If con Is Nothing Then
                Throw New Exception("Cannot connect to database")
            End If
            Dim da As New SqlClient.SqlDataAdapter
            da.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM " & dt.TableName & " WHERE 1=0", con)
            Dim cb As New SqlClient.SqlCommandBuilder(da)
            da.Update(dt)
        Catch ex As Exception
            Throw New Exception(ex.ToString())
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
            End If
        End Try
    End Sub
End Class
